﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using SMSModels;
using System.Data.SQLite;

namespace DAL
{
     public class GoodsService
    {

        

        

        

        



        

        

        


        



        






       

        


        

        

        


        




        


        

        

        


        


        

        

        





        /// <summary>
        /// 获取现有库存list数据
        /// </summary>
        /// <returns></returns>
        public List<Storage> GetListForStorageTable()
        {
            String sql = "select 库存表.货物编号,货物档案表.货物名称,仓库信息表.仓库ID,仓库信息表.仓库名称,供货商信息表.供货商ID,供货商信息表.供货商名称,货物档案表.规格,货物档案表.计量单位 from 库存表,仓库信息表,供货商信息表,货物档案表 where 库存表.货物编号=货物档案表.货物编号 and 库存表.仓库ID=仓库信息表.仓库ID and 库存表.供货商ID=供货商信息表.供货商ID";
            SQLiteDataReader reader = SQLHelper.GetReader(sql);
            List<Storage> list = new List<Storage>();
            while (reader.Read())
            {
                list.Add(new Storage
                { 
                    GoodsID=reader["货物编号"].ToString(),
                    GoodsName= reader["货物名称"].ToString(),
                    StoreID =Convert.ToInt32(reader["仓库ID"]),
                    StoreName=reader["仓库名称"].ToString(),
                    SupplierID= Convert.ToInt32( reader["供货商ID"]),
                    SupplierName= reader["供货商名称"].ToString(),
                    Spec=reader["规格"].ToString(),
                    Units=reader["计量单位"].ToString()
                });
            }
            return list;
        }






        /// <summary>
        /// 出库 选择货物编号触发
        /// </summary>
        /// <param name="goodsID"></param>
        /// <returns></returns>
        public List<Storage> GetListOnSelectGoodsNum_outStore(String goodsID)
        {
            String sql = String.Format("select a.货物名称,a.仓库ID,a.仓库名称,a.供货商ID,a.供货商名称,a.规格,a.计量单位 from (select 库存表.货物编号,货物档案表.货物名称,仓库信息表.仓库ID,仓库信息表.仓库名称,供货商信息表.供货商ID,供货商信息表.供货商名称,货物档案表.规格,货物档案表.计量单位 from 库存表,仓库信息表,供货商信息表,货物档案表 where 库存表.货物编号=货物档案表.货物编号 and 库存表.仓库ID=仓库信息表.仓库ID and 库存表.供货商ID=供货商信息表.供货商ID) a where 货物编号='{0}'", goodsID);
            SQLiteDataReader reader = SQLHelper.GetReader(sql);
            List<Storage> list = new List<Storage>();
            while (reader.Read())
            {
                list.Add(new Storage
                {
                    GoodsName = reader["货物名称"].ToString(),
                    //StoreID = Convert.ToInt32(reader["仓库ID"]),
                    StoreName = reader["仓库名称"].ToString(),
                    //SupplierID = Convert.ToInt32(reader["供货商ID"]),
                    SupplierName = reader["供货商名称"].ToString(),
                    Spec = reader["规格"].ToString(),
                    Units = reader["计量单位"].ToString()

                });
            }
            return list;
        }
    }
}
